package com.myersinfosys.protrack.poi;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;


public class XLSReader {
	private static final String DEFAULT_CHARSET = "UTF-8";
	private static final int MAX_COLUMNS = 100;
	private static final String UNL_DELIMITER = "|";
	private static final SimpleDateFormat xlDateFormat = new SimpleDateFormat("MM/dd/yyyy");
	
	private String filename;
	private List<List<HSSFCell>> sheetData;
	private FileInputStream filestream = null;
	private HSSFWorkbook workbook;
	private HSSFSheet sheet;
	private int sheetNumber = 0;
	
	public Boolean hasData = false;
	
	
	public XLSReader(String filename) {
		File f = new File(filename);
		String s = f.getName();
		int pos = s.lastIndexOf(".");
		String ext = s.substring(pos+1).toLowerCase();
		
		if(ext.equals("xlsx")) {
			hasData = false;
		} else {
			this.filename = filename;
			
			sheetData = new ArrayList<List<HSSFCell>>();
			
			try {
				this.readData();
				hasData = true;
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	private Boolean readData() throws IOException {
        try {
            //
            // Create a FileInputStream that will be use to read the excel file.
            //
        	filestream = new FileInputStream(filename);

            //
            // Create an excel workbook from the file system.
            //
            workbook = new HSSFWorkbook(filestream);
            
            //
            // Get the Nth sheet on the workbook.
            //
            sheet = workbook.getSheetAt(sheetNumber);

            //
            // When we have a sheet object in hand we can iterator on each
            // sheet's rows and on each row's cells. We store the data read
            // on an ArrayList
            //
            Iterator<HSSFRow> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<HSSFCell> cells = row.cellIterator();

                List<HSSFCell> data = new ArrayList<HSSFCell>();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
            
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (filestream != null) {
            	filestream.close();
            }
        }		
        
        return true;
	}
	

    public void writeUnlData(String unlFilename) {
    	
    	String cellData = null;
    	BufferedWriter unlFile = null;


    	try {
			unlFile = createOutFile(unlFilename);	

	        //
	        // Iterates the data and prints it out to a unl file.
	        //
			// rows
	        for (int i = 1; i < sheetData.size(); i++) {
	            List<?> list = (List<?>) sheetData.get(i);
	            // columns
	            for (int j = 0; j < list.size() && j < MAX_COLUMNS; j++) {
	            	if (j == 0) {
	            		// write out the id
	            		cellData = new Integer(i).toString();
	            		unlFile.write(cellData + UNL_DELIMITER);
	            		System.out.print(cellData + UNL_DELIMITER);
	            	}
	            		
	                HSSFCell cell = (HSSFCell) list.get(j);
	                cellData = getCellAsString(cell);
	                
	                cellData = cellData.replace(UNL_DELIMITER, " ");
					unlFile.write(cellData + UNL_DELIMITER);
					
	                System.out.print(cellData + UNL_DELIMITER);
	            }
	            unlFile.newLine();
				
	            System.out.println("");
	        }

	        if (unlFile != null) {
	        	unlFile.flush();
	        	unlFile.close();
	        } 
		} catch (IOException e) {
			e.printStackTrace();
		}
 
    }	

    public void writeSqlCreate(String sqlFilename) {
    	
    	int colCount = 0;
    	String cellData;
    	int[] colSize = new int[MAX_COLUMNS];
    	String[] colHeader = new String[MAX_COLUMNS];
    	String tableName = "";
 
    	
    	try {
	        //
	        // Iterates the data and prints it out to a sql file.
	        //
	        for (int i = 0; i < sheetData.size(); i++) {
	            List<?> list = (List<?>) sheetData.get(i);
	            
	            for (int j = 0; j < list.size() && j < MAX_COLUMNS; j++) {
	                HSSFCell cell = (HSSFCell) list.get(j);
	                cellData = getCellAsString(cell);

	                if(i == 0) {
	                	colCount = (list.size()<MAX_COLUMNS) ? list.size() : MAX_COLUMNS;
	            		colSize[j] = 1;
	            		colHeader[j] = cell.getRichStringCellValue().getString().trim();
	            		colHeader[j] = colHeader[j].replace(" ", "_");
	            		if(colHeader[j].length() == 0) {
	            			colHeader[j] = Character.toString((char)(65+j));
	            		}
	                } else {
	                	byte[] nbytes = cellData.getBytes(DEFAULT_CHARSET);
	                	if(nbytes.length > colSize[j])
	                		colSize[j] = nbytes.length;
	                }
	                
	 
	             }
	         }
	        
	        tableName = new File(filename).getName().trim().toLowerCase();
	        int index = tableName.lastIndexOf('.');
	        if (index > 0 && index <= tableName.length() - 2) 
	        	tableName = tableName.substring(0, index);
	        
	        if(tableName.length() > 7) 
	        	tableName = "xl";
	        else 
	        	tableName = "xl" + tableName;
	        
	        	
	        String sBuf = "CREATE TABLE " + tableName + " (id integer,";
	        for (int j = 0; j < colCount; j++) {
	        	sBuf = sBuf + colHeader[j].toLowerCase() + 
	        		    " char(" + colSize[j] + ")" + ((j<colCount-1) ? "," : ");");
	        }
        
	        BufferedWriter sqlFile = createOutFile(sqlFilename);
        	
            if (sqlFile != null) {
            	sqlFile.write(sBuf);
            	sqlFile.flush();
            	sqlFile.close();
            	System.out.println(sBuf);
            }            

		} catch (IOException e) {
			e.printStackTrace();
		}

    }	
    
    
    private BufferedWriter createOutFile(String fileName) {
    	BufferedWriter buffFile = null;

    	try {
			buffFile = new BufferedWriter(
						new OutputStreamWriter(
								new FileOutputStream(fileName), DEFAULT_CHARSET));
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	
    	return buffFile;
    }
    
    
    private String getCellAsString(HSSFCell cell) {
    	String cellData;
    	
        switch(cell.getCellType()) {
	    	case HSSFCell.CELL_TYPE_NUMERIC :
	    		if(HSSFDateUtil.isCellDateFormatted(cell))
	    	        cellData = xlDateFormat.format(cell.getDateCellValue());
	    	    else
	    	    	cellData = Double.toString(cell.getNumericCellValue());
	    		break;
	    	case HSSFCell.CELL_TYPE_STRING :
	    		cellData = cell.getRichStringCellValue().getString().trim();
	    		break;
	    	case HSSFCell.CELL_TYPE_BOOLEAN:
	            cellData = Boolean.toString(cell.getBooleanCellValue());
	            break;
	        case HSSFCell.CELL_TYPE_FORMULA:
	            cellData = cell.getCellFormula();
	            break;
	    	default : 
	    		cellData = "";
	    		break;
        }    	
        
        return cellData.trim();
    }
    

}
